<?php

class mreturns extends CI_Model {
    
    public function getTerritoryId(){
		$this->db->select('territory_id');
		$this->db->from('t_mtr_territory');
		$this->db->where('user_id', $this->session->userdata('username'));
		$query = $this->db->get();

        return $query->row();
	}
	
	public function getReturn($return_id){
		$this->db->select('*');
		$this->db->from('t_trx_sell_return');
		$this->db->where('return_id', $return_id);
		$query = $this->db->get();

        return $query->row();
	}
	
	public function getSalesName($sales_id){
		$this->db->select('user_name');
		$this->db->from('t_mtr_user');
		$this->db->where('user_id', $sales_id);
		$query = $this->db->get();

        return $query->row();
	}
	
	public function getOutletName($outlet_id){
		$this->db->select('outlet_name');
		$this->db->from('t_mtr_outlet');
		$this->db->where('outlet_id', $outlet_id);
		$query = $this->db->get();

        return $query->row();
	}
	
	/*public function getItems($return_id){
		$this->db->select('t_trx_sell_return_detail.iccid, t_trx_sell_in_detail.created_on, t_mtr_item.item_name, t_trx_sales_order_detail.price');
		//$this->db->select('t_trx_sell_return_detail.iccid, t_trx_sell_in_detail.created_on, t_mtr_item.item_name, t_mtr_item.default_price');
		$this->db->from('t_trx_sell_return_detail');
		$this->db->join('t_trx_sell_in_detail', 't_trx_sell_return_detail.iccid = t_trx_sell_in_detail.iccid');
		$this->db->join('t_mtr_item', 't_trx_sell_return_detail.iccid = t_mtr_item.iccid');
		$this->db->join('t_trx_sales_order_detail', 't_trx_sell_return_detail.iccid = t_trx_sales_order_detail.iccid');
		//$this->db->join('t_trx_sales_order_detail', 't_trx_sell_return_detail.iccid = t_trx_sales_order_detail.iccid');
		$this->db->where('t_trx_sell_return_detail.return_id', $return_id);
		$query = $this->db->get();

        return $query->result();
	}*/
	

	public function getRelatedIccid($testValue) {
		$dbconn  = $this->load->database("default", TRUE);
		$userlogin = $this->session->userdata('username');
		$sql = "SELECT so.iccid
				FROM t_trx_sales_order_detail so
				JOIN t_mtr_item item ON so.iccid = item.iccid 
				JOIN t_trx_sell_in_detail si on si.iccid = so.iccid
				WHERE so.iccid like '%$testValue%'";

        $query = $dbconn->query($sql);
        if( $query->num_rows() > 0 ) {
            return $query->result();
        } else {
            return array();
        }
	}
	public function getItemSold($iccid) {
		$dbconn  = $this->load->database("default", TRUE);
		$userlogin = $this->session->userdata('username');
		$sql = "SELECT so.iccid, so.price, so.discount, item.item_name, si.created_on, item.item_code, grp.item_group_name
				FROM t_trx_sales_order_detail so
				JOIN t_mtr_item item ON so.iccid = item.iccid 
				JOIN t_mtr_item_group grp ON item.item_group_id = grp.item_group_id
				JOIN t_trx_sell_in_detail si on si.iccid = so.iccid
				WHERE so.iccid = '$iccid'";

        $query = $dbconn->query($sql);
        if( $query->num_rows() > 0 ) {
            return $query->result();
        } else {
            return array();
        }
	}


	public function getItems($return_id){
		$this->db->select('return_detail_id, iccid, istatus');
		$this->db->from('t_trx_sell_return_detail');
		$this->db->where('return_id', $return_id);
		$query = $this->db->get();

        return $query->result();
	}
	
	public function getItemsOutstandingPrint($return_id){
		$this->db->select('iccid');
		$this->db->from('t_trx_sell_return_detail');
		$this->db->where('return_id', $return_id);
		$this->db->where('istatus', 0);
		$query = $this->db->get();

        if( $query->num_rows() > 0 ) {
            return $query->result();
        } else {
            return array();
        }
	}
	
	public function getItemsSwapPrint($return_id){
		$this->db->select('iccid');
		$this->db->from('t_trx_sell_return_detail');
		$this->db->where('return_id', $return_id);
		$this->db->where('istatus !=', 0);
		//$this->db->where('istatus', -5);
		$query = $this->db->get();

        if( $query->num_rows() > 0 ) {
            return $query->result();
        } else {
            return array();
        }
	}
	
	public function getItemsSwapIn($return_id)
	{
		$this->db->select('iccid, istatus');
		$this->db->from('t_trx_sell_return_detail');
		$this->db->where('return_id', $return_id);
		$query = $this->db->get();

        return $query->result();
	}
	
	public function getSwapItemCandidate($iccid)
	{
		$dbconn  = $this->load->database("default", TRUE);
		$userlogin = $this->session->userdata('username');
		$sql = "SELECT itm.iccid, itm.item_name, itm.item_code, grp.item_group_name
				FROM t_mtr_item itm
				JOIN t_mtr_item_mapping imap ON itm.item_code = imap.item_code
				JOIN t_mtr_item_group grp ON imap.item_group_id = grp.item_group_id
				JOIN t_mtr_distributor dis ON itm.distributor_id = dis.distributor_id
				WHERE dis.territory_id = f_get_cluster('$userlogin')
				AND itm.iccid like '%$iccid%'
				AND itm.istatus = 1 LIMIT 10";

        $query = $dbconn->query($sql);
        if( $query->num_rows() > 0 ) {
            return $query->result();
        } else {
            return array();
        }
		
		
	}
	
	public function getItemsSwap($return_id)
	{
		$this->db->select('t_trx_sell_return_swap.iccid, t_mtr_item.item_code, t_mtr_item.item_name, t_mtr_item_group.item_group_name');
		$this->db->from('t_trx_sell_return_swap');
		$this->db->join('t_mtr_item', 't_trx_sell_return_swap.iccid = t_mtr_item.iccid');
		$this->db->join('t_mtr_item_mapping', 't_mtr_item.item_code = t_mtr_item_mapping.item_code');
		$this->db->join('t_mtr_item_group', 't_mtr_item_mapping.item_group_id = t_mtr_item_group.item_group_id');
		$this->db->join('t_trx_sell_return', 't_trx_sell_return_swap.return_id = t_trx_sell_return.return_id');
		$this->db->where('t_trx_sell_return_swap.return_id', $return_id);
		$query = $this->db->get();

        return $query->result();
		
	}
	
    /*public function getCluster(){
       	$this->db->select('distributor_name, distributor_address, t_mtr_distributor.territory_id');
		$this->db->from('t_mtr_distributor');
		$this->db->join('t_mtr_user_territory', 't_mtr_distributor.territory_id = t_mtr_user_territory.territory_id', 'left');
		$this->db->join('t_mtr_territory', 't_mtr_distributor.territory_id = t_mtr_territory.territory_id', 'left');
        $this->db->join('t_mtr_territory_type', 't_mtr_territory.territory_type_id = t_mtr_territory_type.territory_type_id AND t_mtr_territory_type.territory_type_id = 3', 'left');
		$this->db->where('t_mtr_user_territory.user_id', $this->session->userdata('username'));
        $this->db->limit(1, 0);
		$query = $this->db->get();

        return $query->result();
        
    }*/
	
	public function getCluster()
	{
		$ter = $this->session->userdata('username');
		$this->db->select("f_get_territory_path( f_get_cluster( '$ter' ) ) as territory_name");
		// $this->db->select('territory_name, territory_id');
		// $this->db->from('t_mtr_territory');
		// $this->db->where('user_id', $this->session->userdata('username'));
		// $this->db->limit(1, 0);
		$query = $this->db->get();

		return $query->result();
	}
    
    /*public function getSales($territory_id)
    {
        $this->db->select('t_mtr_user.user_id, t_mtr_user.user_name');
		$this->db->from('t_mtr_user');
		$this->db->join('t_mtr_user_group', 't_mtr_user.user_group_id = t_mtr_user_group.user_group_id AND t_mtr_user.istatus = 1 AND t_mtr_user_group.having_stock = 1', 'left');
	    $this->db->join ('t_mtr_territory', 't_mtr_territory.user_id = t_mtr_user.user_id AND t_mtr_territory.parent_id = '.$territory_id);
		$this->db->order_by("t_mtr_user.user_name", "asc");
		$query = $this->db->get();
        return $query->result();
    }*/
	
	public function getSales()
    {
	
	    $dbconn  = $this->load->database("default", TRUE);
		$userlogin = $this->session->userdata('username');
		$sql = "SELECT DISTINCT ter.user_id, usr.user_name
				FROM t_mtr_territory ter
				JOIN t_mtr_user usr on ter.user_id = usr.user_id
				AND ter.parent_id = f_get_cluster('$userlogin')
				UNION
				SELECT DISTINCT ut.user_id, usr.user_name
				FROM t_mtr_user_territory ut
				JOIN t_mtr_user usr on ut.user_id = usr.user_id
				AND usr.reporting_to = '$userlogin'
				ORDER BY user_name";

        $query = $dbconn->query($sql);
        if( $query->num_rows() > 0 ) {
            return $query->result();
        } else {
            return array();
        }
        
    }
	
	public function getOutlet($sales_id)
    {
		//$sales_id = $this->input->post('sales_id');
        $this->db->select('t_mtr_outlet.outlet_id, t_mtr_outlet.outlet_name');
		$this->db->from('t_mtr_outlet');
		$this->db->join('t_mtr_territory', 't_mtr_outlet.territory_id = t_mtr_territory.territory_id');
		$this->db->where('t_mtr_territory.user_id', $sales_id);
		$this->db->order_by("t_mtr_outlet.outlet_name", "asc");
		$query = $this->db->get();
        return $query->result_array();
    }
	
	public function getItemName()
    {
        $this->db->select('iccid, item_name');
		$this->db->from('t_mtr_item');
		
	    $this->db->order_by("t_mtr_item.iccid", "asc");
		$query = $this->db->get();
        return $query->result();
    }
	
	public function getSwapPrintNama($cekid)
	{
		$this->db->select('item_name');
		$this->db->from('t_mtr_item');
		$this->db->where('iccid', $cekid);
	    $query = $this->db->get();
		
		if ($query->num_rows() > 0)
		{
		   $row = $query->row(); 
		   return $row->item_name;
		}
		else{
			return "N/A";
		}
        
	}
	
	public function getSwapPrintPrice($cekid)
	{
		$this->db->select('price');
		$this->db->from('t_trx_sales_order_detail');
		$this->db->where('iccid', $cekid);
	    $query = $this->db->get();
		
		if ($query->num_rows() > 0)
		{
		   $row = $query->row(); 
		   return $row->price;
		}
		else{
			return "N/A";
		}
	}
	
	
	public function getPrice()
    {
	    $dbconn  = $this->load->database("default", TRUE);
		$userlogin = $this->session->userdata('username');
		$sql = "SELECT h.price, h.discount, h.iccid, d.item_name
				FROM t_trx_sales_order_detail h
				JOIN t_mtr_item d ON d.iccid = h.iccid
				ORDER BY h.iccid ASC";

        $query = $dbconn->query($sql);
        if( $query->num_rows() > 0 ) {
            return $query->result();
        } else {
            return array();
        }

  //       $this->db->select('iccid, price, discount');
		// $this->db->from('t_trx_sales_order_detail');
		
	 //    $this->db->order_by("t_trx_sales_order_detail.iccid", "asc");
		// $query = $this->db->get();
  //       return $query->result();
    }
	
	public function getBuyingDate()
    {

		// select si.iccid, si.created_on
		// from t_trx_sell_in_detail si
		// join t_trx_sales_order_detail so on si.iccid = so.iccid
	    $dbconn  = $this->load->database("default", TRUE);
		$userlogin = $this->session->userdata('username');
		$sql = "SELECT si.iccid, si.created_on
				FROM t_trx_sell_in_detail si
				JOIN t_trx_sales_order_detail so on si.iccid = so.iccid
				ORDER BY si.iccid ASC";

        $query = $dbconn->query($sql);
        if( $query->num_rows() > 0 ) {
            return $query->result();
        } else {
            return array();
        }

        $this->db->select('iccid, created_on');
		$this->db->from('t_trx_sell_in_detail');
		//$where = "'istatus'=1 OR 'istatus'=2";
		$this->db->where('istatus >', 0);
		//$this->db->where($where);
	    $this->db->order_by("t_trx_sell_in_detail.iccid", "asc");
		$query = $this->db->get();
        return $query->result();
    }
	
		function getIccid($term)
		{
			
			//$this->db->distinct();
			$this->db->select('iccid');
			$this->db->from('t_trx_sell_in_detail');
			$this->db->like('iccid', $term, 'after');
			$this->db->where('istatus >', 0);
			$query = $this->db->get();
			
			if( $query->num_rows() > 0 ) {
				return $query->result();
			} 
			else {
				return array();
			}	
		}

	
	public function getBuyingDateDetail()
    {
        $this->db->select('iccid, created_on');
		$this->db->from('t_trx_sell_in_detail');
		//$where = "'istatus'=1 OR 'istatus'=2";
		$this->db->where('istatus ', -10);
		//$this->db->where($where);
	    $this->db->order_by("t_trx_sell_in_detail.iccid", "asc");
		$query = $this->db->get();
        return $query->result();
    }
    
	public function getSwapPrintDate($cekid)
	{
		$this->db->select('created_on');
		$this->db->from('t_trx_sell_in_detail');
		$this->db->where('iccid', $cekid);
	    $query = $this->db->get();
		
		if ($query->num_rows() > 0)
		{
		   $row = $query->row(); 
		   return $row->created_on;
		}
		else{
			return "N/A";
		}
	}
	
	public function getSwapPrintStatus($cekid, $return_id)
	{
		$this->db->select('istatus');
		$this->db->from('t_trx_sell_return_detail');
		$this->db->where('iccid', $cekid);
		$this->db->where('return_id', $return_id);
	    $query = $this->db->get();
		
		if ($query->num_rows() > 0)
		{
		   $row = $query->row(); 
		   if($row->istatus == 1){
				return "Valid";
		   }
		   else if($row->istatus == -5){
				return "Invalid";
		   }
		   
		}
		else{
			return "N/A";
		}
	}
	
    public function getItemCode($iccid)
    {
        $this->db->select('item_code');
		$this->db->from('t_mtr_item');
		$this->db->where('iccid', $iccid);
		$query = $this->db->get();
        return $query->result();
    }
    
    public function saveReturnOutstanding($return_id, $data) {
    	$this->db->trans_start();
        $dblokal = $this->load->database("default", true);
		foreach ($data['return_detail_iccid'] as $key) {
			// print_r($key);
        	$dblokal->update("t_trx_sell_return_detail", array("istatus" => 1), array('iccid' => $key) );
		}
        // $dblokal->update("t_trx_sell_return", array("istatus" => 2), array('return_id' => $return_id) );
        if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
        } else {
            $this->db->trans_commit();

        }
    }

    function saveReturn($return_date, $sales_id, $channel_id, $remark)
    {
		$this->db->trans_start();
        $dblokal = $this->load->database("default", true);
        //$return_date = $this->input->post("return_date");
        //$sales_id = $this->input->post("sales_id");
		
		//$territory = $this->getTerritoryId();
		//$territory_id = $territory->territory_id;
		//$channel_id = $this->input->post("outlet_id");
		
        $status = 1;
        //$remark = '';
		//if ($this->input->post("remark")){
		//	$remark = $this->input->post("remark");
		//}

        $data = array(
        	"return_date" => $return_date, 
			"created_by" => $this->session->userdata('username'),
			"updated_by" => $this->session->userdata('username'),
        	"user_id" => $sales_id,
            "istatus" => $status, 
            "channel_id" => $channel_id, 
            "remark" => $remark,
            "access_via" => 'web'
            );
		
        $dblokal->insert("t_trx_sell_return", $data);
        if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
        } else {
            $this->db->trans_commit();

        }
        $return_id = $this->getReturnNo();
        return $return_id;
        
    }
    
	function cekSellIn($iccid)
	{
		$this->db->select('istatus');
		$this->db->from('t_trx_sell_in_detail');
		$this->db->where('iccid', $iccid);
		$query = $this->db->get();
        
        return $query->num_rows(); 
	}
	
    function saveReturnDetail($iccid, $return_id)
    {
		$this->db->trans_start();
        $dblokal = $this->load->database("default", true);
        $data['istatus'] = 1;
        $data['iccid'] = $iccid;
        $data['return_id'] = $return_id;
        
        $dblokal->insert("t_trx_sell_return_detail", $data);
		
		//$data1 = array("istatus" => -10);
		//$this->db->update('t_trx_sell_in_detail', $data1, array('iccid' => $iccid));
		
		$cek = $this->cekSellIn($iccid);
		if($cek > 0){
			$data1 = array("istatus" => -10);
			$this->db->update('t_trx_sell_in_detail', $data1, array('iccid' => $iccid));
		}
        if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
        } else {
            $this->db->trans_commit();

        }
		$code = $this->getItemCode($iccid);
		$item_code = "";
		foreach($code as $item){
			$item_code = $item->item_code;
		}
		
		$string_data = $return_id.",".$outlet_id.",".$sales_id.",".$iccid.",".$item_code.",0\r\n";
        return $string_data;
    }
	
	function saveSwapDetail($return_id, $iccid)
	{
		$this->db->trans_start();
		$dblokal = $this->load->database("default", true);
		$status = 0;
        $data = array(
			"created_by" => $this->session->userdata('username'),
			"updated_by" => $this->session->userdata('username'),
        	"return_id" => $return_id, 
        	"iccid" => $iccid
        	);
        $dblokal->insert("t_trx_sell_return_swap", $data);
		
		$data1 = array("istatus" => 3);
		$this->db->update('t_mtr_item', $data1, array('iccid' => $iccid));
		
		$data2 = array("istatus" => 3);
		$this->db->update('t_trx_sell_return', $data2, array('return_id' => $return_id));
        if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
        } else {
            $this->db->trans_commit();

        }
	}
    
    function getReturnNo()
    {
        $this->db->select('*')->from('t_trx_sell_return');
        $this->db->order_by('return_id', 'desc');
        $query = $this->db->get();   
 
        return $query->row();    
        
    }
    

    // ambil semua return outstanding
    public function getAllOutstanding() {
        //get all records from users table
        //$query = $this->db->get( 't_menu' );
		//$territory = $this->getTerritoryId();
		//$territory_id = $territory->territory_id;
		$user_id = $this->session->userdata('username');

		$this->db->select("f_get_cluster('$user_id') AS clusterid");
		$cluster_id = $this->db->get()->row();

        $this->db->select('t_trx_sell_return.return_id, t_trx_sell_return.return_date, t_mtr_user.user_name, t_mtr_outlet.outlet_name');
		$this->db->from('t_trx_sell_return');
		$this->db->join('t_mtr_user', 't_trx_sell_return.user_id = t_mtr_user.user_id');
		$this->db->join('t_mtr_outlet', 't_trx_sell_return.channel_id = t_mtr_outlet.outlet_id');
		$this->db->where('t_trx_sell_return.istatus ', 1);
		// AND f_get_cluster("t_trx_sell_return"."user_id") = f_get_cluster('10003817')
		$this->db->where('f_get_cluster(t_trx_sell_return.user_id)', $cluster_id->clusterid);
		// $this->db->order_by("t_trx_sell_return.return_date", "asc");
		//$this->db->order_by("t_trx_sell_return.return_id", "asc");
		$query = $this->db->get();
        //return $query->result_array();
		
        if( $query->num_rows() > 0 ) {
            return $query->result();
        } else {
            return array();
        }
    } //end getAll
	
	public function getAllSwap() {
        
        $this->db->select('t_trx_sell_return.return_id, t_trx_sell_return.return_date, t_mtr_user.user_name, t_mtr_outlet.outlet_name');
		$this->db->from('t_trx_sell_return');
		$this->db->join('t_mtr_user', 't_trx_sell_return.user_id = t_mtr_user.user_id');
		$this->db->join('t_mtr_outlet', 't_trx_sell_return.channel_id = t_mtr_outlet.outlet_id');
		$this->db->where('t_trx_sell_return.istatus', 2);
		//$this->db->order_by("t_trx_sell_return.return_id", "asc");
		$query = $this->db->get();
        //return $query->result_array();
		
        if( $query->num_rows() > 0 ) {
            return $query->result();
        } else {
            return array();
        }
    } //end getAll
	
	public function getAllSuccess() {
       
        $this->db->select('t_trx_sell_return.return_id, t_trx_sell_return.return_date, t_mtr_user.user_name, t_mtr_outlet.outlet_name');
		$this->db->from('t_trx_sell_return');
		$this->db->join('t_mtr_user', 't_trx_sell_return.user_id = t_mtr_user.user_id');
		$this->db->join('t_mtr_outlet', 't_trx_sell_return.channel_id = t_mtr_outlet.outlet_id');
		$this->db->where('t_trx_sell_return.istatus', 3);
		//$this->db->order_by("t_trx_sell_return.return_id", "asc");
		$query = $this->db->get();
        //return $query->result_array();
		
        if( $query->num_rows() > 0 ) {
            return $query->result();
        } else {
            return array();
        }
    } //end getAll
    

    public function returned($return_id) {
    	$this->db->join("t_mtr_outlet","t_mtr_outlet.outlet_id = t_trx_sell_return.channel_id");
    	$this->db->join("t_mtr_user","t_mtr_user.user_id = t_trx_sell_return.user_id");
    	$this->db->where("t_trx_sell_return.return_id", $return_id);
    	$query = $this->db->get("t_trx_sell_return");

    	return $query->row();
    }
    public function returnedItems($return_id) {
		$this->db->select("t_trx_sell_return_detail.return_detail_id, t_trx_sell_return_detail.iccid, t_trx_sell_return_detail.istatus");
		$this->db->select(", t_trx_sales_order_detail.price, t_trx_sales_order_detail.discount");
		$this->db->select("t_trx_sell_in_detail.created_on, t_mtr_item_mapping.item_name");
		$this->db->join("t_trx_sales_order_detail","t_trx_sales_order_detail.iccid = t_trx_sell_return_detail.iccid");
		$this->db->join("t_trx_sell_in_detail","t_trx_sell_in_detail.iccid=t_trx_sell_return_detail.iccid");
		$this->db->join("t_mtr_item","t_mtr_item.iccid = t_trx_sell_return_detail.iccid");
		$this->db->join("t_mtr_item_mapping","t_mtr_item_mapping.item_code = t_mtr_item.item_code");
		$this->db->where("t_trx_sell_return_detail.return_id", $return_id);

    	$query = $this->db->get("t_trx_sell_return_detail");

    	return $query->result();
    }

	public function getReturn2($return_id){
		$this->db->select('*, f_get_channel_name( t_trx_sell_return.channel_id )');
		$this->db->from('t_trx_sell_return');
		$this->db->join("t_mtr_user","t_mtr_user.user_id = t_trx_sell_return.user_id");
		$this->db->where('return_id', $return_id);
		$query = $this->db->get();
		return $query->row();
	}

	public function getSwapItem($iccid) {
		$dbconn  = $this->load->database("default", TRUE);
		$userlogin = $this->session->userdata('username');
		$sql = "SELECT item.iccid, item.item_name, item.item_code, grp.item_group_name
				FROM t_mtr_item item
				JOIN t_mtr_item_group grp ON item.item_group_id = grp.item_group_id
				WHERE item.iccid = '$iccid'";

        $query = $dbconn->query($sql);
        if( $query->num_rows() > 0 ) {
            return $query->result();
        } else {
            return array();
        }
	}
} //end class